from fastapi import APIRouter, Request, HTTPException
from fastapi.responses import StreamingResponse, JSONResponse
from pydantic import BaseModel
from math import ceil

from database.database import engine
from sqlalchemy import text

from utils.smart_sales_db import select_category_info
from .goods import select_goods_category_count, select_goods_category_limit, select_goods_temporary

router = APIRouter(
    prefix='/category',
    tags = ['产品类目']
)

def select_category_head():
    # try:
    with engine.connect() as conn:
        sql = text(f'''
        select * from smart_sales_category where level = 1;
        ''')
        result = conn.execute(sql).fetchall()
    return result
    # except:
    #   pass


def select_category_children(parent):
    # try:
    with engine.connect() as conn:
        sql = text(f'''
        select id, name, picture, level, parent, null as children, null as goods from smart_sales_category where parent = :parent;
        ''')
        result = conn.execute(sql, [{'parent': parent}]).fetchall()
    return result
    # except:
    #   pass


def save_category_info(record):
    # try:
    with engine.connect() as conn:
        sql = text(f'''
        insert into smart_sales_category(name, picture, level, parent) values (:name, :picture, :level, :parent);
        ''')
        conn.execute(sql, [record])
        conn.commit()
    # except:
    #   pass


def delete_category_info(id):
    # try:
    with engine.connect() as conn:
        sql = text(f'''
        delete from smart_sales_category where id = :id;
        ''')
        conn.execute(sql, [{"id":id}])
        conn.commit()
    # except:
    #   pass


@router.get("")
def category_info(id: int):
    result = select_category_info(id)
    if result:
        category_dict = result._asdict()
        category_children = select_category_children(category_dict["id"])
        children = []
        for child in category_children:
            children_dict = child._asdict()
            children_dict["children"] = []
            children_goods = select_goods_category_limit(children_dict["id"], 5)
            children_dict["goods"] = [child._asdict() for child in children_goods]
            children.append(children_dict)
        category_dict["children"] = children
        # category_goods = select_goods_category_limit(id, 8)
        category_dict["goods"] = [] # [child._asdict() for child in category_goods]

        data = { "code": 0, "message": "操作成功" }
        data["result"] = category_dict
        return JSONResponse(status_code=200, content=data)
    else:
        data = { "code": 2, "msg": "操作失败" }
        return JSONResponse(status_code=201, content=data)


@router.post("/head")
def category_head():
    result = select_category_head()

    data = {}
    data["list"] = [child._asdict() for child in result]
    data["total"] = len(result)
    content = { "code": 0, "message": "操作成功" }
    content["data"] = data

    return JSONResponse(status_code=200, content=content)


@router.post("/list")
def category_list():
    result = select_category_head()
    result_list = []
    total = 0
    for row in result:
        category_dict = row._asdict()
        category_children = select_category_children(category_dict["id"])
        category_dict["children"] = [child._asdict() for child in category_children]
        total += len(category_children) + 1
        result_list.append(category_dict)

    data = {}
    data["list"] = result_list
    data["total"] = total
    content = { "code": 0, "message": "操作成功" }
    content["data"] = data

    return JSONResponse(status_code=200, content=content)


@router.post("/save")
def category_save(record:dict=None):
    parent = record.get("parent")
    if parent:
        record["level"] = select_category_info(parent)._asdict()["level"] + 1
    else:
        record["level"] = 1
    record["parent"] = record.get("parent")
    save_category_info(record)
    content = { "code": 0, "message": "操作成功" }
    return JSONResponse(status_code=200, content=content)


@router.delete("/delete")
def category_delete(id: int):
    delete_category_info(id)
    content = { "code": 0, "message": "操作成功" }
    return JSONResponse(status_code=200, content=content)



@router.get("/{child}")
def category_children(child: int):
    result = select_category_children(child)
    if result:
        result_dict = [row._asdict() for row in result]
        # category_goods = select_goods_category_limit(child, 5)
        # goods_dict = [child._asdict() for child in category_goods]

        data = { "code": 0, "message": "操作成功" }
        data["result"] = result_dict
        data["goods"] = goods_dict
        return JSONResponse(status_code=200, content=data)
    else:
        data = { "code": 2, "msg": "操作失败" }
        return JSONResponse(status_code=201, content=data)


@router.get("/sub/filter")
def category_sub_filter(id: int):
    result = select_category_info(id)
    if result:
        category_dict = result._asdict()
        category_parent = select_category_info(category_dict["parent"])._asdict()
        category_dict["parentName"] = category_parent["name"]
        # category_goods = select_goods_category_limit(id, 5)
        # category_dict["goods"] = [child._asdict() for child in category_goods]

        data = { "code": 0, "message": "操作成功" }
        data["result"] = category_dict
        return JSONResponse(status_code=200, content=data)
    else:
        data = { "code": 2, "msg": "操作失败" }
        return JSONResponse(status_code=201, content=data)


@router.post("/goods/temporary")
def category_goods_temporary(query:dict):
    if query:
        counts = select_goods_category_count(query["categoryId"])
        records = select_goods_temporary(query)
        items = [row._asdict() for row in records]

        data = { "code": 0, "message": "操作成功" }
        result = {
            "counts": counts,
            "items": items,
            "page": query["page"],
            "pageSize": query["pageSize"],
            "pages": ceil(counts / query["pageSize"])
        }
        data["result"] = result
        return JSONResponse(status_code=200, content=data)
    else:
        data = { "code": 2, "msg": "操作失败" }
        return JSONResponse(status_code=201, content=data)